<?php
class ModelSaleDelivery extends Model {
	public function addDelivery($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "delivery ".
			" SET postcode_default = '" . $this->db->escape($data['postcode']) . "', ".
			" city = '" . $this->db->escape($data['city']) . "', ".
			" price = '" . $this->db->escape($data['price']) . "'"
		);
	}
	
	public function editDelivery($delivery_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "delivery ".
			" SET postcode_default = '" . $this->db->escape($data['postcode']) . "', ".
			" city = '" . $this->db->escape($data['city']) . "', ".
			" price = '" . $this->db->escape($data['price']) . "' ".
			"WHERE delivery_id = '" . (int)$delivery_id . "'"
		);
	}
	
	public function deleteDelivery($delivery_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "delivery WHERE delivery_id = '" . (int)$delivery_id . "'");		
	}
	
	public function getDelivery($delivery_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "delivery WHERE delivery_id = '" . (int)$delivery_id . "'");
		
		return $query->row;
	}
	
	public function getDeliveries($data = array()) {
		$sql = "SELECT * FROM " . DB_PREFIX . "delivery WHERE delivery_id > 0";

        if (!empty($data['filter_city'])) {
            $sql .= " AND city LIKE '" . $this->db->escape($data['filter_city']) . "%'";
        }
        if (!empty($data['filter_price'])) {
            $sql .= " AND price = '" . $data['filter_price'] . "'";
        }

		if(isset($data['sort'])){
			$sql .= " ORDER BY ".$data['sort'];
		}
        else{
			$sql .= " ORDER BY delivery_id";
		}
		
		if(isset($data['order'])){
			if($data['order'] == 'DESC') {
				$sql .= " DESC";
			}
            else{
				$sql .= " ASC";
			}
		}
		
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
			
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);
		
		return $query->rows;
	}
	
	public function getTotalDeliveries($data = array()) {
		$sql = 'SELECT COUNT(*) AS total FROM ' . DB_PREFIX . 'delivery WHERE delivery_id > 0';

        if (!empty($data['filter_city'])) {
            $sql .= " AND city LIKE '" . $this->db->escape($data['filter_city']) . "%'";
        }
        if (!empty($data['filter_price'])) {
            $sql .= " AND price = '" . $data['filter_price'] . "'";
        }

        $query = $this->db->query($sql);

		return $query->row['total'];
	}
	
	public function addPostcode($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "delivery_postcode ".
			" SET postcode = '" . $this->db->escape($data['postcode']) . "', ".
			" delivery_id = '" . $this->db->escape($data['delivery_id']) . "'"			
		);
	}
	
	public function editPostcode($postcode_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "delivery_postcode ".
			" SET postcode = '" . $this->db->escape($data['postcode']) . "', ".
			" delivery_id = '" . $this->db->escape($data['delivery_id']) . "' ".			
			"WHERE postcode_id = '" . (int)$postcode_id . "'"
		);
	}
	
	public function deletePostcode($postcode_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "delivery_postcode WHERE postcode_id = '" . (int)$postcode_id . "'");		
	}
	
	public function getPostcode($postcode_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "delivery_postcode WHERE postcode_id = '" . (int)$postcode_id . "'");
		
		return $query->row;
	}
	
	public function getPostcodes($data = array()) {
		$sql = 'SELECT * FROM ' . DB_PREFIX . 'delivery_postcode p INNER JOIN delivery d ON p.delivery_id = d.delivery_id WHERE d.delivery_id > 0';

        if (!empty($data['filter_postcode'])) {
            $sql .= " AND p.postcode = '" . $data['filter_postcode'] . "'";
        }
        if (!empty($data['filter_city'])) {
            $sql .= " AND d.delivery_id = '" . (int)$data['filter_city'] . "'";
        }

		if (isset($data['sort']) && ($data['sort'] == 'DESC')) {
			$sql .= " ORDER BY ";
		} else {
			$sql .= " ORDER BY ".$data['sort'];
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
			
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
			
		$query = $this->db->query($sql);
		
		return $query->rows;
	}
	
	public function getTotalPostcodes($data = array()) {
        //$sql = 'SELECT COUNT(*) AS total FROM ' . DB_PREFIX . 'delivery_postcode WHERE postcode_id > 0';
        $sql = 'SELECT COUNT(*) AS total FROM ' . DB_PREFIX . 'delivery_postcode p INNER JOIN delivery d ON p.delivery_id = d.delivery_id WHERE d.delivery_id > 0';

        if (!empty($data['filter_postcode'])) {
            $sql .= " AND p.postcode = '" . $data['filter_postcode'] . "'";
        }
        if (!empty($data['filter_city'])) {
            $sql .= " AND d.delivery_id = '" . (int)$data['filter_city'] . "'";
        }

        $query = $this->db->query($sql);

		return $query->row['total'];
	}
}
?>